import pandas as pd
from sklearn.linear_model import Ridge
from sklearn.model_selection import train_test_split
from sklearn.preprocessing import StandardScaler
from sklearn.metrics import mean_squared_error

df = pd.read_excel('F:/25MCM_C/C_data/CHN_USA.xlsx', engine='openpyxl')
#比赛结束前最后一天售后群发布无水印可视化结果+无标注代码【可直接提交】为了防止倒卖， 论文写作过程中遗留数个致命问题，无关代码，该问题解决方式仅在官网授权售后群答疑，盗卖方式购买资料不提供答疑，感谢理解 美赛资料助攻购买链接+说明https://docs.qq.com/doc/p/f3dc6bffbf4dab58dbdfd3e5e5de18a2ad974216
# 创建一个新的列，表示教练更换的时间，2013年后美国教练更换，2015年后中国教练更换
df['coach_change'] = df.apply(lambda row: 1 if (row['NOC'] == 'USA' and row['YEAR'] >= 2013) or (
            row['NOC'] == 'CHN' and row['YEAR'] >= 2015) else 0, axis=1)

df['medal_weight'] = df['MEDAL'].map({'Gold': 3, 'Silver': 2, 'Bronze': 1})

df['total_medal_weight'] = df.groupby(['YEAR', 'NOC'])['medal_weight'].transform('sum')

print(f"Missing values in 'total_medal_weight': {df['total_medal_weight'].isna().sum()}")  # 输出奖牌总权重列中的缺失值数量

df = df.dropna(subset=['total_medal_weight'])

print(f"Unique NOC values: {df['NOC'].unique()}")#比赛结束前最后一天售后群发布无水印可视化结果+无标注代码【可直接提交】为了防止倒卖， 论文写作过程中遗留数个致命问题，无关代码，该问题解决方式仅在官网授权售后群答疑，盗卖方式购买资料不提供答疑，感谢理解 美赛资料助攻购买链接+说明https://docs.qq.com/doc/p/f3dc6bffbf4dab58dbdfd3e5e5de18a2ad974216

df_usa = df[df['NOC'] == 'USA']
df_china = df[df['NOC'] == 'CHN']

print(f"Number of USA records: {df_usa.shape[0]}")
print(f"Number of China records: {df_china.shape[0]}")

if df_china.shape[0] == 0:
    print("Warning: No data for China (NOC == 'CHN')")
#比赛结束前最后一天售后群发布无水印可视化结果+无标注代码【可直接提交】为了防止倒卖， 论文写作过程中遗留数个致命问题，无关代码，该问题解决方式仅在官网授权售后群答疑，盗卖方式购买资料不提供答疑，感谢理解 美赛资料助攻购买链接+说明https://docs.qq.com/doc/p/f3dc6bffbf4dab58dbdfd3e5e5de18a2ad974216
# 创建自变量X和因变量y
def prepare_data(df):
    X = pd.get_dummies(df[['coach_change', 'YEAR', 'NOC']], drop_first=True)  # 将分类变量进行独热编码
    y = df['total_medal_weight']  # 使用奖牌总权重作为因变量
    # 数据标准化
    scaler = StandardScaler()
    X_scaled = scaler.fit_transform(X)
    return X_scaled, y

# 处理美国和中国数据
if df_usa.shape[0] > 0:
    X_usa, y_usa = prepare_data(df_usa)
else:
    X_usa, y_usa = None, None
#比赛结束前最后一天售后群发布无水印可视化结果+无标注代码【可直接提交】为了防止倒卖， 论文写作过程中遗留数个致命问题，无关代码，该问题解决方式仅在官网授权售后群答疑，盗卖方式购买资料不提供答疑，感谢理解 美赛资料助攻购买链接+说明https://docs.qq.com/doc/p/f3dc6bffbf4dab58dbdfd3e5e5de18a2ad974216
if df_china.shape[0] > 0:
    X_china, y_china = prepare_data(df_china)
else:
    X_china, y_china = None, None

# 如果数据存在，继续建模
if X_usa is not None and X_china is not None:
    # 划分训练集和测试集
    X_usa_train, X_usa_test, y_usa_train, y_usa_test = train_test_split(X_usa, y_usa, test_size=0.2, random_state=42)
    X_china_train, X_china_test, y_china_train, y_china_test = train_test_split(X_china, y_china, test_size=0.2, random_state=42)

    ridge_model_usa = Ridge(alpha=1.0)
    ridge_model_usa.fit(X_usa_train, y_usa_train)

    ridge_model_china = Ridge(alpha=1.0)
    ridge_model_china.fit(X_china_train, y_china_train)

    y_usa_pred = ridge_model_usa.predict(X_usa_test)
    y_china_pred = ridge_model_china.predict(X_china_test)
    # 比赛结束前最后一天售后群发布无水印可视化结果+无标注代码【可直接提交】为了防止倒卖， 论文写作过程中遗留数个致命问题，无关代码，该问题解决方式仅在官网授权售后群答疑，盗卖方式购买资料不提供答疑，感谢理解 美赛资料助攻购买链接+说明https://docs.qq.com/doc/p/f3dc6bffbf4dab58dbdfd3e5e5de18a2ad974216
    mse_usa = mean_squared_error(y_usa_test, y_usa_pred)
    mse_china = mean_squared_error(y_china_test, y_china_pred)

    print("USA Model:")
    print("Coefficients:", ridge_model_usa.coef_)
    print("Intercept:", ridge_model_usa.intercept_)

    print("\nChina Model:")
    print("Coefficients:", ridge_model_china.coef_)
    print("Intercept:", ridge_model_china.intercept_)

    print("\nCoach Change Effect Comparison:")
    print(f"USA coach change effect: {ridge_model_usa.coef_[0]}")
    print(f"China coach change effect: {ridge_model_china.coef_[0]}")
else:
    print("Error: No data for either USA or China.")
